package sql

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

func InitDB() *sql.DB {
	dsn := "root:453924241@tcp(localhost:3306)/test"

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}

	err = db.Ping()
	if err != nil {
		panic(err)
	}

	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(2)

	return db
}

type user struct {
	id   int
	name string
	age  int
}

func Insert(db *sql.DB) {
	var sqlInsert = "insert into user(`name`, age) values (?,?)"
	users := []user{
		{0, "aa", 18},
		{0, "bb", 19},
		{0, "cc", 20},
	}

	for _, u := range users {
		result, err := db.Exec(sqlInsert, u.name, u.age)
		if err != nil {
			fmt.Println(err)
			continue
		}

		fmt.Println(result.LastInsertId())
	}
}

func QueryRow(db *sql.DB) {
	s := "select id, `name`, age from user where id = ?"
	var u user
	row := db.QueryRow(s, 1)

	row.Scan(&u.id, &u.name, &u.age)

	fmt.Println(u)
}

func QueryList(db *sql.DB) {
	s := "select * from user where id >= ?"
	rows, err := db.Query(s, 1)
	if err != nil {
		fmt.Println(err)
		return
	}
	defer rows.Close()

	var users []user
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Println(err)
			return
		}

		users = append(users, u)
	}

	fmt.Println(users)
}

func Update(db *sql.DB) {
	s := "update user set age = 100 where id = ?"

	result, _ := db.Exec(s, 1)
	fmt.Println(result.RowsAffected())
}

func Delete(db *sql.DB) {
	s := "delete from user where id = ?"
	result, _ := db.Exec(s, 3)
	fmt.Println(result.RowsAffected())
}

func PrepareQuery(db *sql.DB) {
	s := "select * from user where id >= ?"

	stmt, _ := db.Prepare(s)
	defer stmt.Close()
	rows, err := stmt.Query(1)
	if err != nil {
		fmt.Println(err)
		return
	}
	defer rows.Close()

	var users []user
	for rows.Next() {
		var u user
		rows.Scan(&u.id, &u.name, &u.age)
		users = append(users, u)
	}

	fmt.Println(users)
}

func PrepareInsert(db *sql.DB) {
	var sqlInsert = "insert into user(name, age) values (?,?)"
	users := []user{
		{0, "aaa", 181},
		{0, "bba", 191},
		{0, "cca", 201},
	}

	for _, u := range users {
		stmt, _ := db.Prepare(sqlInsert)
		r, _ := stmt.Exec(u.name, u.age)
		stmt.Close()
		fmt.Println(r.LastInsertId())
	}
}

func PrepareUpdate(db *sql.DB) {
	s := "update user set age = 1000 where id = ?"
	stmt, _ := db.Prepare(s)
	defer stmt.Close()

	r, _ := stmt.Exec(6)
	fmt.Println(r.RowsAffected())
}

func TransactionDemo(db *sql.DB) error {
	tx, err := db.Begin()

	defer func() {
		if err1 := recover(); err1 != nil {
			fmt.Println(err1)
			tx.Rollback()
			fmt.Println("事务回滚")
		}
		if err != nil {
			fmt.Println(err)
			tx.Rollback()
			fmt.Println("事务回滚")
		}
	}()

	s1 := "update user set name = ? where id = ?"
	statement1, _ := tx.Prepare(s1)
	defer statement1.Close()
	_, err = statement1.Exec("fanshuai", 1)

	statement2, _ := tx.Prepare(s1)
	defer statement2.Close()
	_, err = statement2.Exec("linfangyi", 2)

	tx.Commit()
	fmt.Println("事务提交")

	return err
}
